{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 初期設定"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ライブラリ\n",
    "\n",
    "* openpyxl\n",
    "    * エクセルの読み書きができる\n",
    "    * 既存のExcelファイルの書式を保ったまま編集・追記したい場合に便利\n",
    "* pandas.read_excel()\n",
    "    * pandasでエクセルをpandas.DataFrameとして読み込む\n",
    "* xlrd/xlwt\n",
    "    * エクセルの読み/書きができる\n",
    "    * xlrdはpandas.read_excel()の内部で使われている"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## インストール\n",
    "\n",
    "pandas.read_excel()はpandasがインストールされていれば使えるので略\n",
    "\n",
    "```\n",
    "pip install openpyxl xlrd xlwt\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# openpyxl\n",
    "\n",
    "+ pandasは使ってない"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 基本"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl as excel\n",
    "\n",
    "# 新規のワークブック作成\n",
    "wb = excel.Workbook()\n",
    "\n",
    "# アクティブなワークシートを得る\n",
    "ws = wb.active\n",
    "\n",
    "# A1セルに値を代入\n",
    "ws[\"A1\"] = \"こんにちは\"\n",
    "\n",
    "# 2行2列目に値を代入\n",
    "ws.cell(column=2, row=2, value=\"さようなら\")\n",
    "\n",
    "# ファイルを保存\n",
    "wb.save(\"excel/hello.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### エクセルファイルの読み込み\n",
    "\n",
    "* load_workbook\n",
    "    * data_only : 数式ではなく値のみ取り出す"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "こんにちは\n",
      "((<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>), (<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>), (<Cell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>))\n",
      "<Cell 'Sheet'.B2>\n",
      "さようなら\n"
     ]
    }
   ],
   "source": [
    "import openpyxl as excel\n",
    "\n",
    "# エクセルの読み込み\n",
    "wb = excel.load_workbook(\"excel/hello.xlsx\", data_only=True)\n",
    "\n",
    "# シートを選択\n",
    "ws = wb[\"Sheet\"]\n",
    "\n",
    "# 値の参照\n",
    "print(ws[\"A1\"].value)\n",
    "\n",
    "# 任意の範囲を選択\n",
    "list_data = ws[\"A1:C3\"]\n",
    "print(list_data)\n",
    "print(list_data[1][1])\n",
    "print(list_data[1][1].value)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# pandasで利用"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## エクセルファイルの読み込み\n",
    "\n",
    "* sheet_name\n",
    "    * None : 全シートを読み込む\n",
    "    * (シート名) : (シート名)を読み込む"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OrderedDict([('number',       1    2     3\n",
      "0   4.0  5.0  6.00\n",
      "1   7.0  8.0  9.00\n",
      "2   NaN  NaN   NaN\n",
      "3   1.2 -5.0  3.14\n",
      "4   3.0  NaN   NaN\n",
      "5  12.0  NaN   NaN), ('charactor',      a    b    c\n",
      "0    d    e    f\n",
      "1    g    h    i\n",
      "2  NaN  NaN  NaN\n",
      "3    !    ~    #\n",
      "4    $    %    &), ('日本語',     おはよう\n",
      "0  こんにちは\n",
      "1  さようなら)])\n",
      "--------------------------\n",
      "      1    2     3\n",
      "0   4.0  5.0  6.00\n",
      "1   7.0  8.0  9.00\n",
      "2   NaN  NaN   NaN\n",
      "3   1.2 -5.0  3.14\n",
      "4   3.0  NaN   NaN\n",
      "5  12.0  NaN   NaN\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(\"excel/read_excel.xlsx\", sheet_name=None)\n",
    "print(df)\n",
    "print(\"--------------------------\")\n",
    "print(df[\"number\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "OrderedDict([('number',       1    2     3\n",
       "              0   4.0  5.0  6.00\n",
       "              1   7.0  8.0  9.00\n",
       "              2   NaN  NaN   NaN\n",
       "              3   1.2 -5.0  3.14\n",
       "              4   3.0  NaN   NaN\n",
       "              5  12.0  NaN   NaN), ('charactor',      a    b    c\n",
       "              0    d    e    f\n",
       "              1    g    h    i\n",
       "              2  NaN  NaN  NaN\n",
       "              3    !    ~    #\n",
       "              4    $    %    &)])"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(\"excel/read_excel.xlsx\", sheet_name=[\"number\", \"charactor\"])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## エクセルファイルへ書き込み\n",
    "\n",
    "* この見た目のようにindexやcolumnは太字でエクセルに記載される\n",
    "* <b>上書きした場合、他のシートの情報も含めて全て削除される</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>おはよう</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>こんにちは</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>さようなら</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    おはよう\n",
       "0  こんにちは\n",
       "1  さようなら"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(\"excel/read_excel.xlsx\", sheet_name=\"日本語\")\n",
    "df.to_excel(\"excel/write_excel_test.xlsx\")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## エクセルファイルへの書き込み(追加)\n",
    "\n",
    "* openpyxlを利用する"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import openpyxl as opx\n",
    "\n",
    "# 書き込み対象ファイルのExcelWriterオブジェクトを取得\n",
    "excel_writer = pd.ExcelWriter(\"excel/write_excel_test.xlsx\", engine=\"openpyxl\")\n",
    "\n",
    "# 読み込み対象ファイルのシートの読み込み、ExcelWriterオブジェクト.bookに渡す\n",
    "#excel_writer.book = opx.load_workbook(\"excel/write_excel_test.xlsx\")\n",
    "excel_writer.book = opx.load_workbook(\"excel/read_excel.xlsx\")\n",
    "\n",
    "# データフレームを追加シートに書き込む\n",
    "df_add = pd.DataFrame([('apple',160),('orange',30),('kiwi',80)], columns=['name', 'price'])\n",
    "df_add.to_excel(excel_writer, sheet_name=\"果物\")\n",
    "excel_writer.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
